package com.gps.web.action.sheets;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import com.gps.web.comm.Unility;
import com.gps.web.comm.action.BaseSheetAction;
import com.gps.web.dao.sheet.VehicleRegistDao;
import com.gps.web.entity.sheets.VehicleRegist;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.xssf.usermodel.*;
import org.apache.struts2.ServletActionContext;

public class VehicleRegistAction extends BaseSheetAction {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	@Resource
	private VehicleRegistDao vrDao;

	static Logger logger = Logger
			.getLogger(VehicleRegistAction.class.getName());

	private List<VehicleRegist> vehicleRegistList;	
	private String registDate;
	private String licensePlateNum;
	private String deviceID;
	private String carNum;
	private String motorcade;

	public String execute() throws IOException, InvalidFormatException {
		vehicleRegistList = vrDao.findAll();
		
		return SUCCESS;
	}
	
	public String search() {
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("registDate", registDate);
		map.put("licensePlateNum", licensePlateNum);
		map.put("deviceID", deviceID);
		map.put("carNum", carNum);
		map.put("motorcade", motorcade);
		
		vehicleRegistList = vrDao.getVehicleRegistByPage(map);
		
		return SUCCESS;
	}
	
	public String uploadSheet() throws Exception {
		if(StringUtils.isBlank(spreadSheetFileName)) {
			setOutMessage("请选择要上传的文件！");
			return INPUT;
		}
		if(!spreadSheetFileName.endsWith(".xls") && !spreadSheetFileName.endsWith(".xlsx")) {
			setOutMessage("文件格式错误，请上传.xls或.xlsx为后缀的excel文件！");
			return INPUT;
		}
		
		String filePath = ServletActionContext.getServletContext().getRealPath(com.gps.web.comm.GlobalConstants.UPLOAD_PATH + spreadSheetFileName);
		
		SaveFile(filePath);

		if(!validateFileData(filePath)) return INPUT;
		
		List<VehicleRegist> vrList = new ArrayList<VehicleRegist>();

		if(!ReadDataToBuildEntity(vrList, filePath)) return INPUT;

		SaveRecords(vrList);

		vehicleRegistList = vrDao.findAll();
		
		return SUCCESS;
	}
	
	private boolean SaveFile(String filePath) throws Exception
	{	
		try {
			if(spreadSheet != null)
			{
				File destFile= new File(filePath);
				Unility.UploadFile(spreadSheet, destFile);
			}
		} catch (Exception e) {
			throw e;
		}
		return true;
	}
	
	@Override
	protected void initSheetHeader() {
		colHeaderList.add(new ColumnHeader("日期"));
		colHeaderList.add(new ColumnHeader("车牌号"));
		colHeaderList.add(new ColumnHeader("设备型号"));
		colHeaderList.add(new ColumnHeader("ID号"));
		colHeaderList.add(new ColumnHeader("车载号"));
		colHeaderList.add(new ColumnHeader("所属车队"));
		colHeaderList.add(new ColumnHeader("经办人"));
		colHeaderList.add(new ColumnHeader("安装员"));
		colHeaderList.add(new ColumnHeader("发动机号"));
		colHeaderList.add(new ColumnHeader("车架号"));
		colHeaderList.add(new ColumnHeader("车身颜色"));
		colHeaderList.add(new ColumnHeader("核定载质量   单位：T "));
		colHeaderList.add(new ColumnHeader("车辆类型"));
		colHeaderList.add(new ColumnHeader("车辆品牌及型号"));
		colHeaderList.add(new ColumnHeader("车主姓名"));
		colHeaderList.add(new ColumnHeader("车主电话"));
		colHeaderList.add(new ColumnHeader("车主身份证号码"));
		colHeaderList.add(new ColumnHeader("第二联系人 姓 名"));
		colHeaderList.add(new ColumnHeader("第二联系人          电 话"));
		colHeaderList.add(new ColumnHeader("所属公司"));
		colHeaderList.add(new ColumnHeader("备注"));
	}

	private boolean validateFileData(String filePath)
			throws FileNotFoundException {
		
		InputStream inp = new FileInputStream(filePath);
		XSSFWorkbook wb;
		try {
			wb = new XSSFWorkbook(inp);
		} catch (Exception e) {
			setOutMessage("文件读取失败，请重新上传！");
			return false;
		}
		XSSFSheet sheet = wb.getSheetAt(0);
		if(sheet == null || sheet.getRow(0) == null){
			setOutMessage("文件内容为空，请重新上传！");
			return false;
		}
		if(!validateSheetHeader(sheet.getRow(0))){
			setOutMessage("文件标题列格式错误，请重新上传！");
			return false;
		}
		
		return true;
	}
	
	private Boolean ReadDataToBuildEntity(List<VehicleRegist> vrList, String filePath)
			throws FileNotFoundException {
		
		InputStream inp = new FileInputStream(filePath);
		XSSFWorkbook wb;
		try {
			wb = new XSSFWorkbook(inp);
		} catch (Exception e) {
			setOutMessage("文件读取失败，请重新上传！");
			return false;
		}
		XSSFSheet sheet = wb.getSheetAt(0);
		
		// 循环行Row
		for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
			XSSFRow row = sheet.getRow(rowNum);
			if (row == null) {
				continue;
			}

			VehicleRegist vr = new VehicleRegist();
			// 循环列Cell
			for (int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++) {
				XSSFCell cell = row.getCell(cellNum);
				if (cell == null) {
					continue;
				}
				switch (cellNum) {
				case 0:
					vr.setRegistDate(cell.getDateCellValue());
					break;
				case 1:
					vr.setLicensePlateNum(getValue(cell));
					break;
				case 2:
					vr.setDeviceModelNum(getValue(cell));
					break;
				case 3:
					vr.setDeviceID(getValue(cell));
					break;
				case 4:
					vr.setCarNum(getValue(cell));
					break;
				case 5:
					vr.setMotorcade(getValue(cell));
					break;
				case 6:
					vr.setAgent(getValue(cell));
					break;
				case 7:
					vr.setInstaller(getValue(cell));
					break;
				case 8:
					vr.setEngineNum(getValue(cell));
					break;
				case 9:
					vr.setVin(getValue(cell));
					break;
				case 10:
					vr.setVehicleColor(getValue(cell));
					break;
				case 11:
					vr.setVerifiedLoadQuality(cell.getNumericCellValue());
					break;
				case 12:
					vr.setVehicleType(getValue(cell));
					break;
				case 13:
					vr.setBrandAndModelNum(getValue(cell));
					break;
				case 14:
					vr.setOwnerName(getValue(cell));
					break;
				case 15:
					vr.setOwnerPhone(getValue(cell));
					break;
				case 16:
					vr.setOwnerIdCardNum(getValue(cell));
					break;
				case 17:
					vr.setSecondaryContact(getValue(cell));
					break;
				case 18:
					vr.setSecondaryContactNum(getValue(cell));
					break;
				case 19:
					vr.setCompany(getValue(cell));
					break;
				case 20:
					vr.setRemarks(getValue(cell));
					break;
				default:
					break;
				}
			}
			vrList.add(vr);
		}
		
		return true;
	}

	private Boolean SaveRecords(List<VehicleRegist> vrList) {
		for (int i = 0; i < vrList.size(); i++) {
			VehicleRegist entity = vrList.get(i);
			vrDao.saveOrUpdate(entity);
		}
		return true;
	}

	private String getValue(XSSFCell cell) {
		if (cell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		} else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
			return new DecimalFormat("#.##").format(cell.getNumericCellValue());
		} else if (cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA) {
			return cell.getCellFormula();
		} else if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
			return cell.getStringCellValue();
		} else {
			return cell.toString();
		}
	}

	public List<VehicleRegist> getVehicleRegistList() {
		return vehicleRegistList;
	}

	public void setVehicleRegistList(List<VehicleRegist> vehicleRegistList) {
		this.vehicleRegistList = vehicleRegistList;
	}

	public String getRegistDate() {
		return registDate;
	}

	public void setRegistDate(String registDate) {
		this.registDate = registDate;
	}

	public String getLicensePlateNum() {
		return licensePlateNum;
	}

	public void setLicensePlateNum(String licensePlateNum) {
		this.licensePlateNum = licensePlateNum;
	}

	public String getDeviceID() {
		return deviceID;
	}

	public void setDeviceID(String deviceID) {
		this.deviceID = deviceID;
	}

	public String getCarNum() {
		return carNum;
	}

	public void setCarNum(String carNum) {
		this.carNum = carNum;
	}

	public String getMotorcade() {
		return motorcade;
	}

	public void setMotorcade(String motorcade) {
		this.motorcade = motorcade;
	}
	
}
